﻿CREATE PROCEDURE CAT.UpdateCustomer
	@id INT = NULL,

	@code VARCHAR(20) = NULL,
	@name VARCHAR(50) = NULL,
	@description VARCHAR(200) = NULL,
	@active BIT = NULL,

	@address VARCHAR(200) = NULL,
	@city VARCHAR(50) = NULL,
	@contact VARCHAR(50) = NULL,
	@cuit VARCHAR(13) = NULL,
	@email VARCHAR(100) = NULL,
	@important BIT = NULL,
	@telephone VARCHAR(50) = NULL,

	@checkValidation BIT = 1
AS
BEGIN
	-----------------------
	-- Common procedures variables
	-----------------------
	DECLARE 
		--Start of the message to be printed at the end of the procedure.
		@finishEndMSG VARCHAR(300),
		--The name of the procedure with its schema.
		@procName VARCHAR(300),
		--The result of the procedure. If all goes ok it will be 0, else it will contain the code
		--of the error that has been thrown.
		@result INT,
		--To be used to store the value of the global variable @@ROWCOUNT
		@rowsCount INT


	SELECT
		@procName = OBJECT_SCHEMA_NAME(@@PROCID) + '.' + OBJECT_NAME(@@PROCID),
		@result = 0
	
	SET @finishEndMSG =  @procName + ' finished.'

	PRINT @procName + ' Parameters:'
		+ '@id: ' + ISNULL(CAST(@id AS VARCHAR), '')
		+ ', @code: ' + ISNULL(@code, '')
		+ ', @name: ' + ISNULL(@name, '')
		+ ', @description: ' + ISNULL(@description, '')
		+ ', @active: ' + ISNULL(CAST(@active AS VARCHAR), '')
		+ ', @address: ' + ISNULL(@address, '')
		+ ', @city: ' + ISNULL(@city, '')
		+ ', @cuit: ' + ISNULL(@cuit, '')
		+ ', @email: ' + ISNULL(@email, '')
		+ ', @important: ' + ISNULL(CAST(@important AS VARCHAR), '')
		+ ', @telephone: ' + ISNULL(@telephone, '')
		+ ', @checkValidation: ' + ISNULL(CAST(@checkValidation AS VARCHAR), '')


	-----------------------
	-- Transaction variables
	-----------------------
	DECLARE
		--This variable is used as a checkpoint for handle nested transactions.
		@rollbackPoint NCHAR(32),
		--Indicates if the transaction to be commited is owned by the current procedure.
		@ownTransaction BIT

	SET @ownTransaction = 0
		

	-----------------------
	-- Parameters defaults
	-----------------------
	IF (@checkValidation IS NULL)
		SET @checkValidation = 1

	SELECT
		@code = LTRIM(RTRIM(@code)),
		@name = LTRIM(RTRIM(@name)),
		@description = LTRIM(RTRIM(@description)),
		@address = LTRIM(RTRIM(@address)),
		@city = LTRIM(RTRIM(@city)),
		@cuit = LTRIM(RTRIM(@cuit)),
		@email = LTRIM(RTRIM(@email)),
		@telephone = LTRIM(RTRIM(@telephone))


	-----------------------
	-- Parameters validation
	-----------------------
	IF (@checkValidation = 1)
	BEGIN
		IF (@result = 0)
		BEGIN
			EXEC @result = CAT.ValidateCustomer
				@id = @id,
				@code = @code,
				@checkExist = 1
		END
	END


	-----------------------
	-- Begin Transaction
	-----------------------
	IF (@result = 0)
	BEGIN
		PRINT @procName + 'Attempt to update a customer.'

		IF (@@TRANCOUNT > 0)
		BEGIN
			SET @rollbackPoint = REPLACE(CONVERT(NCHAR(36), NEWID()), N'-', N'')
			SAVE TRANSACTION @rollbackPoint
		END
		ELSE
		BEGIN
			SET @ownTransaction = 1
			BEGIN TRANSACTION
		END

		SET @result = @@ERROR --Set @result with the current @@Error value.


		-----------------------
		-- Insert
		-----------------------
		IF (@result = 0)
		BEGIN
			UPDATE CAT.Customers
			SET
				Code = CASE WHEN (@code IS NULL) THEN
								Code
							ELSE
								@code
						END,
				Name = CASE WHEN (@name IS NULL) THEN
								Name
							ELSE
								@name
						END,
				[Description] = CASE WHEN (@description IS NULL) THEN
								[Description]
							ELSE
								@description
						END,
				[Address] = CASE WHEN (@address IS NULL) THEN
								[Address]
							ELSE
								@address
						END,
				City = CASE WHEN (@City IS NULL) THEN
								City
							ELSE
								@City
						END,
				Contact = CASE WHEN (@contact IS NULL) THEN
								Contact
							ELSE
								@contact
						END,
				Cuit = CASE WHEN (@cuit IS NULL) THEN
								Cuit
							ELSE
								@cuit
						END,
				Important = CASE WHEN (@important IS NULL) THEN
								Important
							ELSE
								@important
						END,
				Email = CASE WHEN (@email IS NULL) THEN
								Email
							ELSE
								@email
						END,
				Telephone = CASE WHEN (@telephone IS NULL) THEN
								Telephone
							ELSE
								@telephone
						END,
				Active = CASE WHEN (@active IS NULL) THEN
								Active
							ELSE
								@active
						END
			WHERE
				Id = @id

			SELECT
				@rowsCount = @@ROWCOUNT,
				@result = @@ERROR --Set @result with the current @@Error value.

			IF ((@result = 0) AND (@rowsCount <> 0))
			BEGIN
				PRINT @procName + 'Update id "' + CAST(@id AS VARCHAR) + '"'
			END
			ELSE
			BEGIN
				RAISERROR('El registro con id "%d" no fue editado.', 11, 1, @id)
				SET @result = @@ERROR --Set @result with the current @@Error value.	
			END
		END


		-----------------------
		-- End Transaction
		-----------------------

		--If @result = 0, attempt to commit the transaction. Else rollback it.
		IF (@result = 0)
		BEGIN
			IF (@ownTransaction = 1)
			BEGIN
				PRINT @procName + 'Commit transaction'
				COMMIT TRANSACTION
			END
		END
		ELSE
		BEGIN
			IF ((@rollbackPoint IS NOT NULL) AND (XACT_STATE() = 1))
			BEGIN
				PRINT @procName + 'Rollback transaction point ' + @rollbackPoint
				ROLLBACK TRANSACTION @rollbackPoint;
			END
			ELSE IF (@ownTransaction = 1)
			BEGIN
				PRINT @procName + 'Rollback transaction'
				ROLLBACK TRANSACTION;
			END
		END
	END


	-----------------------
	-- Finish procedure
	-----------------------
	IF (@result = 0)
		PRINT @procName + @finishEndMSG
	ELSE 
		PRINT @procName + @finishEndMSG + ' Result: ' + CAST(@result AS VARCHAR)

	RETURN @result
END

GO
GRANT EXECUTE
    ON OBJECT::CAT.UpdateCustomer TO [CatalogEditor]
    AS [dbo];